#include "sqlhelper.h"
#include <QDate>
#include <QTime>
#include <QDebug>
#include <optional>

SqlHelper::SqlHelper(QObject *parent)
    : QObject(parent)
{
    db = QSqlDatabase::addDatabase("QSQLITE");

    openDatabase();
    createTables();
    //fillInPreliminaryData();
}

SqlHelper::~SqlHelper()
{
    closeDatabase();
}

SqlHelper& SqlHelper::getInstance()
{
    static SqlHelper inst;
    return inst;
}

bool SqlHelper::openDatabase()
{
    if (db.isOpen()) {
        qDebug() << "Database already open!";
        return true;
    }

    db.setDatabaseName("dormitory.db");

    if (!db.open()) {
        qDebug() << "Error: Unable to open database: " << db.lastError().text();
        return false;
    }

    qDebug() << "Database opened successfully!";

    return true;
}

void SqlHelper::closeDatabase()
{
    if (db.isOpen()) {
        db.close();
        qDebug() << "Database closed successfully!";
    }
}


void SqlHelper::deleteDataBase()
{
    if (db.isValid()) {
        db.close();
        QSqlDatabase::removeDatabase("dormitory.db");
    }
    if (QFile::exists("dormitory.db")) {
        if (QFile::remove("dormitory.db")) {
            qDebug() << "Database file deleted successfully.";
        } else {
            qDebug() << "Failed to delete database file.";
        }
    } else {
        qDebug() << "Database file does not exist.";
    }
}

QSqlQuery SqlHelper::executeQuery(const QString &queryStr)
{
    QSqlQuery query;
    if (!query.exec(queryStr)) {
        qDebug() << "Error executing query: " << query.lastError().text();
    }
    return query;
}

void SqlHelper::createTables()
{
    QString createUsersTable = R"(
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL
        );
    )";

    QString createStudentsTable = R"(
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            student_id TEXT NOT NULL UNIQUE,
            dormitory_addr TEXT NOT NULL
        );
    )";

    QString createCheckinTable = R"(
        CREATE TABLE IF NOT EXISTS student_checkin (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_name TEXT NOT NULL,
            student_id TEXT NOT NULL,
            checkin_date TEXT NOT NULL,
            checkin_time TEXT,
            status TEXT NOT NULL,
            remarks TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_name) REFERENCES students(name)
        );
    )";
    QString createDormitoryTable = R"(
        CREATE TABLE IF NOT EXISTS dormitory (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dormitory_number TEXT NOT NULL,
            dormitory_name TEXT NOT NULL,
            floor INTEGER NOT NULL,
            total_people INTEGER NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    )";
    QString createDormitoryJobTable = R"(
        CREATE TABLE IF NOT EXISTS dormitory_job (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            employee_name TEXT NOT NULL,
            position TEXT NOT NULL,
            dormitory_id INTEGER NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (dormitory_id) REFERENCES dormitory(id)
        );
    )";
    QString createDormitoryPowerTable = R"(
        CREATE TABLE IF NOT EXISTS dormitory_power (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dormitory_id INTEGER NOT NULL,
            power_usage REAL NOT NULL,
            update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (dormitory_id) REFERENCES dormitory(id)
        );
    )";

    QSqlQuery query(db);
    if (!query.exec(createUsersTable)) {
        qDebug() << "Failed to create users table:" << query.lastError();
    }
    if (!query.exec(createStudentsTable)) {
        qDebug() << "Failed to create students table:" << query.lastError();
    }
    if (!query.exec(createCheckinTable)) {
        qDebug() << "Failed to create checkin table:" << query.lastError();
    }
    if (!query.exec(createDormitoryTable)) {
        qDebug() << "Failed to create dormitory table:" << query.lastError();
    }
    if (!query.exec(createDormitoryJobTable)) {
        qDebug() << "Failed to create dormitory_job table:" << query.lastError();
    }
    if (!query.exec(createDormitoryPowerTable)) {
        qDebug() << "Failed to create dormitory_power table:" << query.lastError();
    }
}

void SqlHelper::fillInPreliminaryData()
{
    if (insertUser("sysadmin", "1234", "系统管理员")) {
        qDebug() << "Successfully inserted user.";
    }
    if (insertUser("busadmin", "1234", "业务管理员")) {
        qDebug() << "Successfully inserted user.";
    }
    if (insertUser("小江", "1234", "用户")) {
        qDebug() << "Successfully inserted user.";
    }
    if (insertUser("小明", "1234", "用户")) {
        qDebug() << "Successfully inserted user.";
    }
    if (insertUser("小红", "1234", "用户")) {
        qDebug() << "Successfully inserted user.";
    }

    if (insertStudent("小江", "20240001", "1号楼 101")) {
        qDebug() << "Successfully inserted student.";
    }
    if (insertStudent("小明", "20240002", "1号楼 102")) {
        qDebug() << "Successfully inserted student.";
    }
    if (insertStudent("小红", "20240003", "2号楼 201")) {
        qDebug() << "Successfully inserted student.";
    }

    if (insertCheckIn("小江")) {
        qDebug() << "Successfully inserted checkin.";
    }
    if (insertCheckIn("小明")) {
        qDebug() << "Successfully inserted checkin.";
    }
    if (insertCheckIn("小红")) {
        qDebug() << "Successfully inserted checkin.";
    }


    if (insertDormitory("A101", "1号楼 101", 1, 4)) {
        qDebug() << "Successfully inserted dormitory.";
    }
    if (insertDormitory("A102", "1号楼 102", 1, 4)) {
        qDebug() << "Successfully inserted dormitory.";
    }
    if (insertDormitory("B201", "2号楼 201", 2, 4)) {
        qDebug() << "Successfully inserted dormitory.";
    }
    if (insertDormitory("B202", "2号楼 202", 2, 4)) {
        qDebug() << "Successfully inserted dormitory.";
    }

    // 添加宿舍人员职位数据
    if (insertJob("小江", "宿舍管理员", 1)) {
        qDebug() << "Successfully inserted job.";
    }
    if (insertJob("小明", "舍长", 2)) {
        qDebug() << "Successfully inserted job.";
    }
    if (insertJob("小红", "宿舍管理员", 3)) {
        qDebug() << "Successfully inserted job.";
    }

    // 添加宿舍电量使用数据
    if (insertPowerUsage(1, 123.5)) {
        qDebug() << "Successfully inserted power usage.";
    }
    if (insertPowerUsage(2, 98.2)) {
        qDebug() << "Successfully inserted power usage.";
    }
    if (insertPowerUsage(3, 110.4)) {
        qDebug() << "Successfully inserted power usage.";
    }
}


std::optional<QVariantList> SqlHelper::AuthLogin(const QString& username, const QString& password)
{
    QString queryStr = QString("SELECT username, password, role FROM users WHERE username = '%1' AND password = '%2'")
            .arg(username).arg(password);

    QSqlQuery query = executeQuery(queryStr);
    if (query.next()) {
        QVariantList row;
        for (int i = 0; i < 3; ++i) {
            row.append(query.value(i));
        }
        return row; // 返回包含登录数据的 optional 对象
    }
    return std::nullopt;
}

bool SqlHelper::insertUser(const QString &username, const QString &password, const QString &role) {
    QSqlQuery query(db);

    query.prepare("SELECT username, password, role FROM users WHERE username = ?");
    query.addBindValue(username);
    if (query.exec()) {
        if (query.next())
            return true;
    }

    query.prepare("INSERT INTO users (username, password, role) VALUES (?, ?, ?)");
    query.addBindValue(username);
    query.addBindValue(password);
    query.addBindValue(role);
    if (!query.exec()) {
        qDebug() << "Failed to insert user:" << query.lastError();
        return false;
    }
    return true;
}

QList<QVariantList> SqlHelper::queryUsers() {
    QList<QVariantList> results;
    QSqlQuery query(db);
    if (query.exec("SELECT username, password, role FROM users")) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 3; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query users:" << query.lastError();
    }
    return results;
}

bool SqlHelper::updateUser(const QString &oldUsername, const QString &newUsername, const QString &newPassword, const QString &newRole)
{
    QSqlQuery query(db);
    QString updateQuery = QString("UPDATE users SET username = :newUsername, password = :newPassword, role = :newRole WHERE username = :oldUsername");
    query.prepare(updateQuery);

    query.bindValue(":newUsername", newUsername);
    query.bindValue(":newPassword", newPassword);
    query.bindValue(":newRole", newRole);
    query.bindValue(":oldUsername", oldUsername);

    if (!query.exec()) {
        qDebug() << "Failed to update user:" << query.lastError();
        return false;
    }

    return true;
}

bool SqlHelper::deleteUser(const QString &username)
{
    QSqlQuery query(db);

    query.prepare("DELETE FROM users WHERE username = :username");
    query.bindValue(":username", username);

    if (query.exec()) {
        return true;
    } else {
        qDebug() << "删除用户失败:" << query.lastError();
        return false;
    }
}


bool SqlHelper::insertStudent(const QString &name, const QString &studentId, const QString &dormitoryAddr) {
    QSqlQuery query(db);

    query.prepare("SELECT name FROM students WHERE name = ?");
    query.addBindValue(name);
    if (query.exec()) {
        if (query.next())
            return true;
    }

    query.prepare("INSERT INTO students (name, student_id, dormitory_addr) VALUES (?, ?, ?)");
    query.addBindValue(name);
    query.addBindValue(studentId);
    query.addBindValue(dormitoryAddr);
    if (!query.exec()) {
        qDebug() << "Failed to insert student:" << query.lastError();
        return false;
    }
    return true;
}

bool SqlHelper::insertCheckIn(const QString& studentName)
{
    QSqlQuery query(db);

    query.prepare(
        "INSERT INTO student_checkin (student_name, student_id, checkin_date, checkin_time, status, remarks) "
        "VALUES (?, (SELECT student_id FROM students WHERE name = ?), ?, ?, '按时归寝', '无')");

    query.addBindValue(studentName);
    query.addBindValue(studentName);
    query.addBindValue(QDate::currentDate());
    query.addBindValue(QTime::currentTime());

    if (!query.exec()) {
        qDebug() << "Failed to insert student_checkin:" << query.lastError();
        return false;
    }
    return true;
}


QList<QVariantList> SqlHelper::queryCheckIn(const QString& studentName) {
    QSqlQuery query(db);
    QList<QVariantList> results;
    QString str = "SELECT id, student_name, student_id, checkin_date, checkin_time, status, remarks, created_at FROM student_checkin ";
    if (!studentName.isEmpty()) {
        str += "WHERE student_name = ?";
    }

    query.prepare(str);

    qDebug() << "Final SQL Query:" << str;
    if (!studentName.isEmpty()) {
        query.addBindValue(studentName);
        qDebug() << "Binding parameter: student_name =" << studentName;
    }

    if (query.exec()) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 8; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query student_checkin:" << query.lastError();
    }
    return results;
}


bool SqlHelper::updateCheckin(int id, const QString& studentName, const QString& studentId, const QString& checkinDate, const QString& checkinTime, const QString& status, const QString& remarks)
{
    QSqlQuery query(db);
    QString updateQuery = QString();
    query.prepare(R"(
        UPDATE student_checkin SET
            student_name = :studentName,
            student_id = :studentId,
            checkin_date = :checkinDate,
            checkin_time = :checkinTime,
            status = :status,
            remarks = :remarks
        WHERE id = :id
    )");

    query.bindValue(":studentName", studentName);
    query.bindValue(":studentId", studentId);
    query.bindValue(":checkinDate", checkinDate);
    query.bindValue(":checkinTime", checkinTime);
    query.bindValue(":status", status);
    query.bindValue(":remarks", remarks);
    query.bindValue(":id", id);

    if (!query.exec()) {
        qDebug() << "Failed to update user:" << query.lastError();
        return false;
    }

    return true;
}

bool SqlHelper::deleteCheckin(int id)
{
    QSqlQuery query(db);

    query.prepare("DELETE FROM student_checkin WHERE id = :id");
    query.bindValue(":id", id);

    if (query.exec()) {
        return true;
    } else {
        qDebug() << "删除记录失败:" << query.lastError();
        return false;
    }
}

// 插入宿舍
bool SqlHelper::insertDormitory(const QString &dormitoryNumber, const QString &dormitoryName, int floor, int totalPeople) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO dormitory (dormitory_number, dormitory_name, floor, total_people) VALUES (?, ?, ?, ?)");
    query.addBindValue(dormitoryNumber);
    query.addBindValue(dormitoryName);
    query.addBindValue(floor);
    query.addBindValue(totalPeople);

    if (!query.exec()) {
        qDebug() << "Failed to insert dormitory:" << query.lastError();
        return false;
    }
    return true;
}

// 查询宿舍
QList<QVariantList> SqlHelper::queryDormitories() {
    QList<QVariantList> results;
    QSqlQuery query(db);
    if (query.exec("SELECT id, dormitory_number, dormitory_name, floor, total_people FROM dormitory")) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 5; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query dormitory:" << query.lastError();
    }
    return results;
}

// 更新宿舍信息
bool SqlHelper::updateDormitory(int id, const QString &dormitoryNumber, const QString &dormitoryName, int floor, int totalPeople) {
    QSqlQuery query(db);
    query.prepare("UPDATE dormitory SET dormitory_number = ?, dormitory_name = ?, floor = ?, total_people = ? WHERE id = ?");
    query.addBindValue(dormitoryNumber);
    query.addBindValue(dormitoryName);
    query.addBindValue(floor);
    query.addBindValue(totalPeople);
    query.addBindValue(id);

    if (!query.exec()) {
        qDebug() << "Failed to update dormitory:" << query.lastError();
        return false;
    }
    return true;
}

// 删除宿舍
bool SqlHelper::deleteDormitory(int id) {
    QSqlQuery query(db);
    query.prepare("DELETE FROM dormitory WHERE id = ?");
    query.addBindValue(id);

    if (!query.exec()) {
        qDebug() << "Failed to delete dormitory:" << query.lastError();
        return false;
    }
    return true;
}


// 插入职位信息
bool SqlHelper::insertJob(const QString &employeeName, const QString &position, int dormitoryId) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO dormitory_job (employee_name, position, dormitory_id) VALUES (?, ?, ?)");
    query.addBindValue(employeeName);
    query.addBindValue(position);
    query.addBindValue(dormitoryId);

    if (!query.exec()) {
        qDebug() << "Failed to insert job:" << query.lastError();
        return false;
    }
    return true;
}

// 查询职位信息
QList<QVariantList> SqlHelper::queryJobs() {
    QList<QVariantList> results;
    QSqlQuery query(db);
    if (query.exec("SELECT id, employee_name, position, dormitory_id FROM dormitory_job")) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 4; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query job:" << query.lastError();
    }
    return results;
}

// 更新职位信息
bool SqlHelper::updateJob(int id, const QString &employeeName, const QString &position, int dormitoryId) {
    QSqlQuery query(db);
    query.prepare("UPDATE dormitory_job SET employee_name = ?, position = ?, dormitory_id = ? WHERE id = ?");
    query.addBindValue(employeeName);
    query.addBindValue(position);
    query.addBindValue(dormitoryId);
    query.addBindValue(id);

    if (!query.exec()) {
        qDebug() << "Failed to update job:" << query.lastError();
        return false;
    }
    return true;
}

// 删除职位信息
bool SqlHelper::deleteJob(int id) {
    QSqlQuery query(db);
    query.prepare("DELETE FROM dormitory_job WHERE id = ?");
    query.addBindValue(id);

    if (!query.exec()) {
        qDebug() << "Failed to delete job:" << query.lastError();
        return false;
    }
    return true;
}


// 插入宿舍电量信息
bool SqlHelper::insertPowerUsage(int dormitoryId, double powerUsage) {
    QSqlQuery query(db);
    query.prepare("INSERT INTO dormitory_power (dormitory_id, power_usage) VALUES (?, ?)");
    query.addBindValue(dormitoryId);
    query.addBindValue(powerUsage);

    if (!query.exec()) {
        qDebug() << "Failed to insert power usage:" << query.lastError();
        return false;
    }
    return true;
}

// 查询宿舍电量信息
QList<QVariantList> SqlHelper::queryPowerUsage() {
    QList<QVariantList> results;
    QSqlQuery query(db);
    if (query.exec("SELECT id, dormitory_id, power_usage, update_time FROM dormitory_power")) {
        while (query.next()) {
            QVariantList row;
            for (int i = 0; i < 4; ++i) {
                row.append(query.value(i));
            }
            results.append(row);
        }
    } else {
        qDebug() << "Failed to query power usage:" << query.lastError();
    }
    return results;
}

// 更新宿舍电量信息
bool SqlHelper::updatePowerUsage(int id, int dormitoryId, double powerUsage) {
    QSqlQuery query(db);
    query.prepare("UPDATE dormitory_power SET dormitory_id = ?, power_usage = ? WHERE id = ?");
    query.addBindValue(dormitoryId);
    query.addBindValue(powerUsage);
    query.addBindValue(id);

    if (!query.exec()) {
        qDebug() << "Failed to update power usage:" << query.lastError();
        return false;
    }
    return true;
}

// 删除宿舍电量信息
bool SqlHelper::deletePowerUsage(int id) {
    QSqlQuery query(db);
    query.prepare("DELETE FROM dormitory_power WHERE id = ?");
    query.addBindValue(id);

    if (!query.exec()) {
        qDebug() << "Failed to delete power usage:" << query.lastError();
        return false;
    }
    return true;
}

